Release 10.1A: OpenEdge Data Management:
SQL Development

Structure of triggers

Like a stored procedure, a trigger has a specification and a body.

The body of a trigger is the same as that of a stored procedure: BEGIN and END delimiters enclosing a Java snippet. The Java code in the snippet defines the triggered action that executes when the trigger is fired.

As with stored procedures, when it processes a CREATE TRIGGER statement, OpenEdge SQL adds wrapper code to create a Java class and method that is invoked when the trigger is fired.

The trigger specification, however, is different from a stored procedure specification. It contains the following elements:

The CREATE clause specifies the name of the trigger. OpenEdge SQL stores the CREATE TRIGGER statement in the database under trigname. It also uses trigname in the name of the Java class that OpenEdge SQL declares to wrap around the Java snippet. The class name uses the format username_trigname_TP, where username is the user name of the database connection that issued the CREATE TRIGGER statement.
The BEFORE or AFTER keywords specify the trigger action time: whether the triggered action implemented by java_snippet executes before or after the triggering INSERT, UPDATE, or DELETE statement.
The INSERT, DELETE, or UPDATE keyword specifies which data modification command activates the trigger. If UPDATE is the trigger event, this clause can include an optional column list. Updates to any of the specified columns will activate the trigger. (Updates to other columns in the table will not activate the trigger.) If UPDATE is the triggering statement and does not include the optional column list, then the UPDATE statement must specify all the table columns in order to activate the trigger.
The ON table_name clause specifies the table for which the specified trigger event activates the trigger. The ON clause cannot specify a view or a remote table.
The optional REFERENCING clause is allowed only if the trigger also specifies the FOR EACH ROW clause. It provides a mechanism for SQL to pass row values as input parameters to the stored procedure implemented by java_snippet. The code in java_snippet uses the getValue method of the NEWROW and OLDROW objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables.
The FOR EACH clause specifies the frequency with which the triggered action implemented by java_snippet executes.
FOR EACH ROW means the triggered action executes once for each row being updated by the triggering statement. CREATE TRIGGER must include the FOR EACH ROW clause if it also includes a REFERENCING clause.
FOR EACH STATEMENT means the triggered action executes only once for the whole triggering statement. FOR EACH STATEMENT is the default.
The IMPORT clause is the same as in stored procedures. It specifies standard Java classes to import.

Example 9–21 shows the elements of a trigger.

Example 9–21: Trigger elements

CREATE TRIGGER BUG_UPDATE_TRIGGER AFTER UPDATE OF STATUS REPORT, PRIORITY ON BUG_INFO REFERENCING OLDROW, NEWROW FOR EACH ROW IMPORT import java.sql.*. ; BEGIN . . . END